package sh.wn36.vehiclemanage.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;
import sh.wn36.vehiclemanage.entity.RefuelingRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import sh.wn36.vehiclemanage.xzy.RefuelingInfo;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xk
 * @since 2021-04-26
 */
@Service
public interface RefuelingRecordMapper extends BaseMapper<RefuelingRecord> {

    @Select("SELECT car.car_no,current_unit.unit_name,refueling_record.*,dictionary.text,driver.`name` FROM refueling_record" +
            " LEFT JOIN car ON car.id = refueling_record.CAR_ID" +
            " LEFT JOIN current_unit ON current_unit.id=refueling_record.OIL_STATION_ID" +
            " LEFT JOIN dictionary ON dictionary.id = refueling_record.OIL_LABEL" +
            " LEFT JOIN driver ON driver.id= refueling_record.OPERATOR ${ew.customSqlSegment}")
    IPage<RefuelingInfo> selectPage(Page<RefuelingInfo> page,@Param(Constants.WRAPPER) QueryWrapper<RefuelingInfo> wrapper);

    @Select("SELECT refueling_record.THIS_MIL,refueling_record.THIS_GAS_VOLUME," +
            "refueling_record.UNIT_PRICE,refueling_record.OIL_LABEL,car.car_no" +
            " FROM refueling_record" +
            " LEFT JOIN car on refueling_record.CAR_ID=car.id WHERE refueling_record.CAR_ID=#{carId}")
    RefuelingInfo findById(Integer carId);

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(refueling_record.total_amount),0) AS car_expenses,\n" +
            "\"加油记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN refueling_record ON refueling_record.car_id = car.id \n" +
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0")
    List<Map<String, Object>> reportList();

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(refueling_record.total_amount),0) AS car_expenses,\n" +
            "\"加油记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN refueling_record ON refueling_record.car_id = car.id \n" +
            "where car.id=#{carId} "+
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0 ")
    List<Map<String, Object>> queryReportByCarId(Integer carId);
}
